﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using Tilver.Common;
using Tilver.Data;
using Weather.BLL.Common;
using Weather.BLL.Entity;
using Weather.DAL.CoWeixin;

namespace Weather.BLL.Weixin
{
    using global::Weather.DAL.CoWeather;

    public class WeatherBO
    {
        #region 潮汐数据
        public IList<WeatherTideInfo> GetWeatherTideInfoDO(string portName, DateTime queryDate)
        {
            return WebCache.Get(WebCache.WeatherTideInfo + portName + queryDate.ToString(), objects => this.GetWeatherTideInfo(portName, queryDate), 300);
        }
        public IList<WeatherTideInfo> GetWeatherTideInfo(string portName, DateTime queryDate)
        {
            /*select * from dbo.Probe_Tide_PerHour order by 日期 desc;*/
            var list = new List<WeatherTideInfo>();
            var para = new List<DbParameter>();

            var factory = DbDataFactory.Create("COWeather");
            var sql = new StringBuilder();
            sql.AppendFormat(" select top 1 * from dbo.Probe_Tide_PerHour where [港口]=@PortName and [日期]=@QueryDate ");

            {
                var p = new SqlParameter("@PortName", SqlDbType.VarChar);
                p.Value = portName;
                para.Add(p);
            }
            {
                var p = new SqlParameter("@QueryDate", SqlDbType.DateTime);
                p.Value = queryDate;
                para.Add(p);
            }
            var sdr = factory.ExecuteReader(sql.ToString(), para);
            while (sdr.Read())
            {
                list.Add(new WeatherTideInfo
                {
                    date = sdr["日期"] is DBNull ? DateTime.Now : sdr["日期"].ToString().ToDateTime(DateTime.Now),
                    PortName = sdr["港口"] is DBNull ? "" : sdr["港口"].ToString(),
                    TH0 = sdr["TH0"] is DBNull ? 0 : sdr["TH0"].ToString().ToInt32(0),
                    TH1 = sdr["TH1"] is DBNull ? 0 : sdr["TH1"].ToString().ToInt32(0),
                    TH2 = sdr["TH2"] is DBNull ? 0 : sdr["TH2"].ToString().ToInt32(0),
                    TH3 = sdr["TH3"] is DBNull ? 0 : sdr["TH3"].ToString().ToInt32(0),
                    TH4 = sdr["TH4"] is DBNull ? 0 : sdr["TH4"].ToString().ToInt32(0),
                    TH5 = sdr["TH5"] is DBNull ? 0 : sdr["TH5"].ToString().ToInt32(0),
                    TH6 = sdr["TH6"] is DBNull ? 0 : sdr["TH6"].ToString().ToInt32(0),
                    TH7 = sdr["TH7"] is DBNull ? 0 : sdr["TH7"].ToString().ToInt32(0),
                    TH8 = sdr["TH8"] is DBNull ? 0 : sdr["TH8"].ToString().ToInt32(0),
                    TH9 = sdr["TH9"] is DBNull ? 0 : sdr["TH9"].ToString().ToInt32(0),
                    TH10 = sdr["TH10"] is DBNull ? 0 : sdr["TH10"].ToString().ToInt32(0),
                    TH11 = sdr["TH11"] is DBNull ? 0 : sdr["TH11"].ToString().ToInt32(0),
                    TH12 = sdr["TH12"] is DBNull ? 0 : sdr["TH12"].ToString().ToInt32(0),
                    TH13 = sdr["TH13"] is DBNull ? 0 : sdr["TH13"].ToString().ToInt32(0),
                    TH14 = sdr["TH14"] is DBNull ? 0 : sdr["TH14"].ToString().ToInt32(0),
                    TH15 = sdr["TH15"] is DBNull ? 0 : sdr["TH15"].ToString().ToInt32(0),
                    TH16 = sdr["TH16"] is DBNull ? 0 : sdr["TH16"].ToString().ToInt32(0),
                    TH17 = sdr["TH17"] is DBNull ? 0 : sdr["TH17"].ToString().ToInt32(0),
                    TH18 = sdr["TH18"] is DBNull ? 0 : sdr["TH18"].ToString().ToInt32(0),
                    TH19 = sdr["TH19"] is DBNull ? 0 : sdr["TH19"].ToString().ToInt32(0),
                    TH20 = sdr["TH20"] is DBNull ? 0 : sdr["TH20"].ToString().ToInt32(0),
                    TH21 = sdr["TH21"] is DBNull ? 0 : sdr["TH21"].ToString().ToInt32(0),
                    TH22 = sdr["TH22"] is DBNull ? 0 : sdr["TH22"].ToString().ToInt32(0),
                    TH23 = sdr["TH23"] is DBNull ? 0 : sdr["TH23"].ToString().ToInt32(0),

                });
            }
            sdr.Close();

            return list;
        }
        #endregion

        #region 实时数据

        public IList<WeatherAwsRealTimeInfo> GetWeatherAwsRealTimeInfoDO(string areaCode, int queryHour = 1)
        {
            return WebCache.Get(WebCache.WeatherAwsRealTimeInfo + areaCode + queryHour.ToString(), objects => this.GetWeatherAwsRealTimeInfo(areaCode, queryHour), 300);
        }

        private IList<WeatherAwsRealTimeInfo> GetWeatherAwsRealTimeInfo(string areaCode, int queryHour = 1)
        {
            if (queryHour < 0) queryHour = 0;
            var now = DateTime.Now.AddHours(-1 * queryHour);//查询最近多少小时前的数据

            var list = new List<WeatherAwsRealTimeInfo>();
            var para = new List<DbParameter>();
            /*
             select P.*,S.站名 as stationname,S.东经 as lon,S.北纬 as lat 
from [Probe_Aws_PerHour] P,T_Station_Area S
where P.stationnum=S.站号 and P.[id] in
(select MAX(id) as id from [Probe_Aws_PerHour] 
where areacode='58553' and [time] in(select top 1 [time] from [Probe_Aws_PerHour] where areacode='58553' order by [id] desc)
group by stationnum having COUNT(stationnum)>1);
             * SELECT  P.*,S.站名 as stationname,S.东经 as lon,S.北纬 as lat
FROM [Probe_Aws_PerHour] P join T_Station_Area S on P.[stationnum]= S.[站号]
where P.[areacode]='58553' and P.[date]=20141111 and P.[time]=2300
             */
            var factory = DbDataFactory.Create("COWeather");
            var sql = new StringBuilder();
            sql.AppendFormat(" SELECT  P.*,S.站名 as stationname,S.东经 as lon,S.北纬 as lat ");
            sql.AppendFormat(" FROM [Probe_Aws_PerHour] P join T_Station_Area S on P.areacode = S.[区域编码] and P.[stationnum]= S.[站号] ");
            sql.AppendFormat(" where P.[areacode]=@areacode and P.[date]=@date and P.[time]=@time ");
            /*
            sql.AppendFormat(" SELECT  P.*,S.站名 as stationname,S.东经 as lon,S.北纬 as lat ");
            sql.Append(" FROM [Probe_Aws_PerHour] P,T_Station_Area S ");
            sql.Append(" where P.stationnum=S.站号 and P.[id] in ");
            sql.Append(" (select MAX(id) as id from [Probe_Aws_PerHour] ");
            sql.Append(" where areacode=@areacode and [time] in(select top 1 [time] from [Probe_Aws_PerHour] where areacode=@areacode order by [id] desc) ");
            sql.Append(" group by stationnum having COUNT(stationnum)>1) ");
            */
            {
                var p = new SqlParameter("@areacode", SqlDbType.VarChar);
                p.Value = areaCode;
                para.Add(p);
            }
            {
                var p = new SqlParameter("@date", SqlDbType.Int);
                p.Value = now.ToString("yyyyMMdd").ToInt32(0);
                para.Add(p);
            }
            {
                var p = new SqlParameter("@time", SqlDbType.NChar);
                p.Value = now.ToString("HH") + "00";
                para.Add(p);
            }
            var sdr = factory.ExecuteReader(sql.ToString(), para);
            while (sdr.Read())
            {
                list.Add(new WeatherAwsRealTimeInfo
                {
                    areacode = areaCode,
                    ff = sdr["ff"] is DBNull ? 0 : sdr["ff"].ToString().ToInt32(0),
                    fd = sdr["fd"] is DBNull ? 0 : sdr["fd"].ToString().ToInt32(0),
                    ffmax = sdr["ffmax"] is DBNull ? 0 : sdr["ffmax"].ToString().ToInt32(0),
                    ffmin = sdr["ffmin"] is DBNull ? 0 : sdr["ffmin"].ToString().ToInt32(0),
                    p = sdr["p"] is DBNull ? 0 : sdr["p"].ToString().ToInt32(0),
                    rain = sdr["rain"] is DBNull ? 0 : sdr["rain"].ToString().ToInt32(0),
                    rh = sdr["rh"] is DBNull ? 0 : sdr["rh"].ToString().ToInt32(0),
                    t = sdr["t"] is DBNull ? 0 : sdr["t"].ToString().ToInt32(0),
                    tmax = sdr["tmax"] is DBNull ? 0 : sdr["tmax"].ToString().ToInt32(0),
                    tmin = sdr["tmin"] is DBNull ? 0 : sdr["tmin"].ToString().ToInt32(0),
                    v = sdr["v"] is DBNull ? 0 : sdr["v"].ToString().ToInt32(0),
                    //observtime = (sdr["date"] is DBNull || sdr["time"] is DBNull) ? DateTime.Now : this.ConvertToDateTime(sdr["date"].ToString() + sdr["time"].ToString()),
                    observtime = sdr["renewtime"] is DBNull ? now : sdr["renewtime"].ToString().ToDateTime(now),
                    stationnum = sdr["stationnum"] is DBNull ? "" : sdr["stationnum"].ToString(),
                    stationname = sdr["stationname"] is DBNull ? "" : sdr["stationname"].ToString(),
                    lat = sdr["lat"] is DBNull ? "" : sdr["lat"].ToString(),
                    lon = sdr["lon"] is DBNull ? "" : sdr["lon"].ToString()
                });
            }
            sdr.Close();

            return list;
        }

        /// <summary>
        /// 获取XML格式的天气数据
        /// </summary>
        /// <param name="stationNum"></param>
        /// <param name="aws_type"></param>
        /// <returns></returns>
        public string GetWeatherXMLDO(string stationNum, int aws_type)
        {
            return WebCache.Get(WebCache.WeatherXML + stationNum + aws_type.ToString(), objects => GetWeatherXML(stationNum, aws_type), 300);
        }
        private string GetWeatherXML(string stationNum, int aws_type)
        {
            string xml = string.Empty;
            var factory = DbDataFactory.Create("COWeather");
            var sql = new StringBuilder();
            sql.AppendFormat(" select top 1 * from [Probe_Aws_Single] ");
            sql.AppendFormat(" where stationnum=@stationnum and aws_type=@awsType ");
            sql.AppendFormat(" order by [id] desc ");
            var para = new List<DbParameter>();
            {
                var p = new SqlParameter("@stationnum", SqlDbType.VarChar);
                p.Value = stationNum;
                para.Add(p);
            }
            {
                var p = new SqlParameter("@awsType", SqlDbType.Int);
                p.Value = aws_type;
                para.Add(p);
            }
            var sdr = factory.ExecuteReader(sql.ToString(), para);
            while (sdr.Read())
            {
                xml = sdr["text"] is DBNull ? "" : sdr["text"].ToString();
            }
            sdr.Close();
            return xml;
            /*
            string xml = string.Empty;
            var mo = new DAL.CoWeixin.Coder天气实况MO();
            var para = new List<DbParameter>();
            {
                var p = new SqlParameter("@stationnum", SqlDbType.VarChar);
                p.Value = stationNum;
                para.Add(p);
            }
            {
                var p = new SqlParameter("@awsType", SqlDbType.Int);
                p.Value = aws_type;
                para.Add(p);
            }
            var eo = mo.Select(" stationnum=@stationnum and aws_type=@awsType ", 1, " id desc ", para).FirstOrDefault();
            if (eo != null)
            {
                xml = eo.Text;
            }
            return xml;
            */
        }
        public IList<WeatherRealTimeData> GetWeatherRealTimeDataDO(string areaCode, string stationNum)
        {
            return WebCache.Get(WebCache.WeatherRealTimeData + areaCode + stationNum, objects => GetWeatherRealTimeData(areaCode, stationNum), 300);
        }
        public IList<WeatherRealTimeData> GetWeatherRealTimeData(string areaCode, string stationNum)
        {
            var list = new List<WeatherRealTimeData>();
            var para = new List<DbParameter>();
            /*
             SELECT TOP 1000 [id] ,[date] ,[time] ,[t] ,[fd] ,[ff] ,[rain] FROM [co_weatherdata].[dbo].[Probe_Aws_PerHour]
  where [areacode] = @areacode and [stationnum]=@stationnum
             */
            var factory = DbDataFactory.Create("COWeather");
            var sql = new StringBuilder();
            sql.AppendFormat(" SELECT TOP 24 [date],[time],[t],[fd],[ff],[rain] FROM [co_weatherdata].[dbo].[Probe_Aws_PerHour]");
            sql.Append(" where [areacode] = @areacode and stationnum = @stationnum order by [id] desc ");

            {
                var p = new SqlParameter("@areacode", SqlDbType.VarChar);
                p.Value = areaCode;
                para.Add(p);
            }
            {
                var p = new SqlParameter("@stationnum", SqlDbType.VarChar);
                p.Value = stationNum;
                para.Add(p);
            }
            var sdr = factory.ExecuteReader(sql.ToString(), para);
            while (sdr.Read())
            {
                list.Add(new WeatherRealTimeData()
                {
                    AreaCode = areaCode,
                    StationNum = stationNum,
                    ObservTimes = (sdr["date"] is DBNull || sdr["time"] is DBNull) ? 0 : ConvertToUnixTime(sdr["date"].ToString() + sdr["time"].ToString()),
                    ExMaxWindV = sdr["ff"] is DBNull ? "" : sdr["ff"].ToString(),
                    Precipitation = sdr["rain"] is DBNull ? "" : (sdr["rain"].ToString().ToDecimal(0) / 10).ToString(),
                    DryBulTemp = sdr["t"] is DBNull ? "" : sdr["t"].ToString(),
                });
            }
            sdr.Close();
            /*
            var factory = DbDataFactory.Create("ZJSZDZDB");
            var sql = new StringBuilder();
            sql.AppendFormat(" SELECT TOP 24 [StationNum],[ObservTimes],[ExMaxWindV],[Precipitation],[DryBulTemp]FROM [tabRealTimeDataMws_{0}]", DateTime.Now.ToString("yyyyMM"));
            sql.Append(" where stationnum = @stationNum order by [ObservTimes] asc ");

            {
                var p = new SqlParameter("@stationnum", SqlDbType.VarChar);
                p.Value = stationNum;
                para.Add(p);
            }
            var sdr = factory.ExecuteReader(sql.ToString(), para);
            while (sdr.Read())
            {
                list.Add(new WeatherRealTimeData()
                {
                    StationNum = sdr["StationNum"] is DBNull ? "" : sdr["StationNum"].ToString(),
                    ObservTimes = sdr["ObservTimes"] is DBNull ? 0 : ConvertToUnixTime(sdr["ObservTimes"].ToString()),
                    ExMaxWindV = sdr["ExMaxWindV"] is DBNull ? "" : sdr["ExMaxWindV"].ToString(),
                    Precipitation = sdr["Precipitation"] is DBNull ? "" : sdr["Precipitation"].ToString(),
                    DryBulTemp = sdr["DryBulTemp"] is DBNull ? "" : sdr["DryBulTemp"].ToString(),
                });
            }
            sdr.Close();
            */
            return list.OrderBy(w => w.ObservTimes).ToList();
        }
        private DateTime ConvertToDateTime(string observTimes)
        {
            DateTime ret = DateTime.Now;
            if (observTimes.Length >= 10)
            {
                var year = observTimes.Substring(0, 4);
                var month = observTimes.Substring(4, 2);
                var day = observTimes.Substring(6, 2);
                var hour = observTimes.Substring(8, 2);
                ret = string.Format("{0}-{1}-{2} {3}:{4}:{5}", year, month, day, hour, "00", "00").ToDateTime(DateTime.Now);
            }
            return ret;
        }
        private long ConvertToUnixTime(string observTimes)
        {
            long ret = 0;
            if (observTimes.Length >= 10)
            {
                var year = observTimes.Substring(0, 4);
                var month = observTimes.Substring(4, 2);
                var day = observTimes.Substring(6, 2);
                var hour = observTimes.Substring(8, 2);
                var t = string.Format("{0}-{1}-{2} {3}:{4}:{5}", year, month, day, hour, "00", "00").ToDateTime(DateTime.Now);
                ret = (t.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
            }
            /*if (observTimes.Length >= 8)
            {
                var year = observTimes.Substring(0, 2);
                var month = observTimes.Substring(2, 2);
                var day = observTimes.Substring(4, 2);
                var hour = observTimes.Substring(6, 2);
                var t = string.Format("20{0}-{1}-{2} {3}:{4}:{5}", year, month, day, hour, "00", "00").ToDateTime(DateTime.Now);
                ret = (t.ToUniversalTime().Ticks - 621355968000000000) / 10000000;
            }*/
            return ret;
        }
        #endregion

        #region 站点信息
        /// <summary>
        /// 获取区域站信息
        /// </summary>
        /// <param name="stationNum"></param>
        /// <returns></returns>
        public DAL.CoWeather.TStationAreaEO GetAreaStationInfoDO(string stationNum)
        {
            return WebCache.Get(WebCache.AreaStationInfo + stationNum, objects => this.GetAreaStationInfo(stationNum), 600);
        }
        private DAL.CoWeather.TStationAreaEO GetAreaStationInfo(string stationNum)
        {

            var mo = new DAL.CoWeather.TStationAreaMO();
            var info = mo.SelectByCoder站号(stationNum, 1, "").FirstOrDefault();
            if (info == null)
            {
                return new TStationAreaEO();
            }
            else
            {
                return info;
            }
        }
        /// <summary>
        /// 获取自动站信息
        /// </summary>
        /// <param name="areacode"></param>
        /// <returns></returns>
        public DAL.CoWeather.TStationAutoEO GetAutoStationInfoDO(string areacode)
        {
            return WebCache.Get(WebCache.AutoStationInfo + areacode, objects => this.GetAutoStationInfo(areacode), 600);
        }
        private DAL.CoWeather.TStationAutoEO GetAutoStationInfo(string areacode)
        {

            var mo = new DAL.CoWeather.TStationAutoMO();
            var info = mo.SelectByCoder站号(areacode, 1, "").FirstOrDefault();
            if (info == null)
            {
                return new TStationAutoEO();
            }
            else
            {
                return info;
            }
        }

        /// <summary>
        /// 获取站点的坐标
        /// </summary>
        /// <param name="stationNum"></param>
        /// <param name="aws_type"></param>
        /// <returns></returns>
        public Tuple<string, string> GetCenterPointDO(string stationNum, int aws_type)
        {
            return WebCache.Get(WebCache.WeatherCenterPoint + stationNum + aws_type.ToString(), objects => GetCenterPoint(stationNum, aws_type), 600);
        }
        public Tuple<string, string> GetCenterPoint(string stationNum, int aws_type)
        {
            /*
            var mo = new DAL.CoWeixin.Coder用户管理MO();
            var eo = mo.SelectByCoder站号(stationNum, 1, "").FirstOrDefault();
            if (eo != null)
            {
                if (aws_type == 0)
                {
                    return new Tuple<string, string>(eo.Coder东经.HasValue ? eo.Coder东经.Value.ToString() : "", eo.Coder北纬.HasValue ? eo.Coder北纬.Value.ToString() : "");
                }
                else
                {
                    return new Tuple<string, string>(eo.Coder东经1.HasValue ? eo.Coder东经1.Value.ToString() : "", eo.Coder北纬1.HasValue ? eo.Coder北纬1.Value.ToString() : "");
                }
            }
            else
            {
                return new Tuple<string, string>("", "");
            }*/

            string lon = "";
            string lat = "";
            var para = new List<DbParameter>();
            var factory = DbDataFactory.Create("COWeather");
            var sql = new StringBuilder();
            sql.AppendFormat(" SELECT * ");
            sql.AppendFormat(" FROM [用户管理] ");
            sql.AppendFormat(" where [站号]=@areacode ");

            {
                var p = new SqlParameter("@areacode", SqlDbType.VarChar);
                p.Value = stationNum;
                para.Add(p);
            }

            var sdr = factory.ExecuteReader(sql.ToString(), para);
            if (sdr.Read())
            {
                if (aws_type == 0)
                {

                    lon = sdr["东经"] is DBNull ? "" : sdr["东经"].ToString();
                    lat = sdr["北纬"] is DBNull ? "" : sdr["北纬"].ToString();

                }
                else
                {

                    lon = sdr["东经1"] is DBNull ? "" : sdr["东经1"].ToString();
                    lat = sdr["北纬1"] is DBNull ? "" : sdr["北纬1"].ToString();

                }

            }
            sdr.Close();
            return new Tuple<string, string>(lon, lat);

        }


        #endregion

        #region 环境监测
        public IList<WeatherAqiStationInfo> GetWeatherAqiStationInfo(string areaCode)
        {
            var list = new List<WeatherAqiStationInfo>();
            var para = new List<DbParameter>();
            /*
             SELECT * FROM [co_weatherdata].[dbo].[T_Station_Aqi]
             */
            var factory = DbDataFactory.Create("COWeather");
            var sql = new StringBuilder();
            sql.AppendFormat(" SELECT * FROM [co_weatherdata].[dbo].[T_Station_Aqi] ");
            sql.AppendFormat(" WHERE [区域编码]=@areacode ");
            {
                var p = new SqlParameter("@areacode", SqlDbType.VarChar);
                p.Value = areaCode;
                para.Add(p);
            }

            var sdr = factory.ExecuteReader(sql.ToString(), para);
            while (sdr.Read())
            {
                list.Add(new WeatherAqiStationInfo
                {
                    areacode = areaCode,
                    stationnum = sdr["站号"] is DBNull ? "" : sdr["站号"].ToString(),
                    stationname = sdr["站名"] is DBNull ? "" : sdr["站名"].ToString(),
                    lat = sdr["北纬"] is DBNull ? "" : sdr["北纬"].ToString(),
                    lon = sdr["东经"] is DBNull ? "" : sdr["东经"].ToString(),
                    province = sdr["省市"] is DBNull ? "" : sdr["省市"].ToString(),
                    city = sdr["地市"] is DBNull ? "" : sdr["地市"].ToString(),
                    county = sdr["县域"] is DBNull ? "" : sdr["县域"].ToString(),

                });
            }
            sdr.Close();

            return list;
        }
        /// <summary>
        /// 获取某个站最近24小时空气质量数据
        /// </summary>
        /// <param name="areaCode"></param>
        /// <param name="stationNum"></param>
        /// <returns></returns>
        public IList<WeatherAqiRealTimeInfo> GetWeatherAqiRealTimeInfoByHourDO(string areaCode, string stationNum)
        {
            return WebCache.Get(WebCache.WeatherAqiRealTimeInfo + areaCode + stationNum, objects => this.GetWeatherAqiRealTimeInfoByHour(areaCode, stationNum), 600);
        }
        public IList<WeatherAqiRealTimeInfo> GetWeatherAqiRealTimeInfoByHour(string areaCode, string stationNum)
        {
            var list = new List<WeatherAqiRealTimeInfo>();
            var para = new List<DbParameter>();
            /*
             SELECT  P.*,S.站名 as stationname,S.东经 as lon,S.北纬 as lat
FROM [Probe_Aqi_PerHour] P join T_Station_Aqi S on P.[stationnum]= S.[站号]
where P.[areacode]=@areacode and P.[date]=@date and P.[time]=@time
             */
            var factory = DbDataFactory.Create("COWeather");
            var sql = new StringBuilder();
            sql.AppendFormat(" SELECT Top 24 P.*,S.站名 as stationname,S.东经 as lon,S.北纬 as lat ");
            sql.AppendFormat(" FROM [Probe_Aqi_PerHour] P join T_Station_Aqi S on P.[stationnum]= S.[站号] ");
            sql.AppendFormat(" WHERE P.[areacode]=@areacode and P.[stationnum]=@stationnum ");
            sql.AppendFormat(" ORDER BY P.[Renewtime] DESC");

            {
                var p = new SqlParameter("@areacode", SqlDbType.VarChar);
                p.Value = areaCode;
                para.Add(p);
            }
            {
                var p = new SqlParameter("@stationnum", SqlDbType.VarChar);
                p.Value = stationNum;
                para.Add(p);
            }
            var sdr = factory.ExecuteReader(sql.ToString(), para);
            while (sdr.Read())
            {
                list.Add(new WeatherAqiRealTimeInfo
                {
                    areacode = areaCode,
                    observtime = sdr["Date"] is DBNull ? DateTime.Now : ConvertToDateTime(string.Format("{0}{1}", sdr["Date"], sdr["Time"])),
                    stationnum = sdr["stationnum"] is DBNull ? "" : sdr["stationnum"].ToString(),
                    stationname = sdr["stationname"] is DBNull ? "" : sdr["stationname"].ToString(),
                    lat = sdr["lat"] is DBNull ? "" : sdr["lat"].ToString(),
                    lon = sdr["lon"] is DBNull ? "" : sdr["lon"].ToString(),
                    Pm1 = sdr["Pm1"] is DBNull ? 0 : sdr["Pm1"].ToString().ToDouble(0),
                    Pm25 = sdr["Pm25"] is DBNull ? 0 : sdr["Pm25"].ToString().ToDouble(0),
                    Pm10 = sdr["Pm10"] is DBNull ? 0 : sdr["Pm10"].ToString().ToDouble(0),
                    Aqi = sdr["Aqi"] is DBNull ? 0 : sdr["Aqi"].ToString().ToDouble(0),
                    SO2 = sdr["SO2"] is DBNull ? 0 : sdr["SO2"].ToString().ToDouble(0),
                    NO2 = sdr["NO2"] is DBNull ? 0 : sdr["NO2"].ToString().ToDouble(0),
                    CO = sdr["CO"] is DBNull ? 0 : sdr["CO"].ToString().ToDouble(0),
                    O3 = sdr["O3"] is DBNull ? 0 : sdr["O3"].ToString().ToDouble(0),
                });
            }
            sdr.Close();

            return list.OrderBy(w => w.observtime).ToList();
        }
        /*
         SELECT [站名],[东经],[北纬],[Pm1],[Pm1_Max],[Pm1_Min],[Pm2_5],[Pm2_5_Max],[Pm2_5_Min],[Pm10],[Pm10_Max],[Pm10_Min]
FROM [Probe_Aqi_PerHour] a join dbo.T_Station_Aqi b on a.[stationnum]=b.[站号]
where [areacode]='58553' and [date]=20141111 and [time]=2300
         */
        /// <summary>
        /// 环境监测数据
        /// </summary>
        /// <param name="areaCode"></param>
        /// <returns></returns>
        public IList<WeatherAqiRealTimeInfo> GetWeatherAqiRealTimeInfoDO(string areaCode, int queryHour = 1)
        {
            return WebCache.Get(WebCache.WeatherAqiRealTimeInfo + areaCode + queryHour.ToString(), objects => this.GetWeatherAqiRealTimeInfo(areaCode, queryHour), 300);
        }
        private IList<WeatherAqiRealTimeInfo> GetWeatherAqiRealTimeInfo(string areaCode, int queryHour = 1)
        {
            if (queryHour <= 0) queryHour = 1;
            var now = DateTime.Now.AddHours(-1 * queryHour);//查询最近多少小时前的数据

            var list = new List<WeatherAqiRealTimeInfo>();
            var para = new List<DbParameter>();
            /*
             SELECT  P.*,S.站名 as stationname,S.东经 as lon,S.北纬 as lat
FROM [Probe_Aqi_PerHour] P join T_Station_Aqi S on P.[stationnum]= S.[站号]
where P.[areacode]=@areacode and P.[date]=@date and P.[time]=@time
             */
            var factory = DbDataFactory.Create("COWeather");
            var sql = new StringBuilder();
            sql.AppendFormat(" SELECT P.*,S.站名 as stationname,S.东经 as lon,S.北纬 as lat ");
            sql.AppendFormat(" FROM [Probe_Aqi_PerHour] P join T_Station_Aqi S on P.[stationnum]= S.[站号] ");
            sql.AppendFormat(" where P.[areacode]=@areacode and P.[date]=@date and P.[time]=@time ");

            {
                var p = new SqlParameter("@areacode", SqlDbType.VarChar);
                p.Value = areaCode;
                para.Add(p);
            }
            {
                var p = new SqlParameter("@date", SqlDbType.Int);
                p.Value = now.ToString("yyyyMMdd").ToInt32(0);
                para.Add(p);
            }
            {
                var p = new SqlParameter("@time", SqlDbType.NChar);
                p.Value = now.ToString("HH") + "00";
                para.Add(p);
            }
            var sdr = factory.ExecuteReader(sql.ToString(), para);
            while (sdr.Read())
            {
                list.Add(new WeatherAqiRealTimeInfo
                {
                    areacode = areaCode,
                    observtime = sdr["renewtime"] is DBNull ? now : sdr["renewtime"].ToString().ToDateTime(now),
                    stationnum = sdr["stationnum"] is DBNull ? "" : sdr["stationnum"].ToString(),
                    stationname = sdr["stationname"] is DBNull ? "" : sdr["stationname"].ToString(),
                    lat = sdr["lat"] is DBNull ? "" : sdr["lat"].ToString(),
                    lon = sdr["lon"] is DBNull ? "" : sdr["lon"].ToString(),

                    Pm1 = sdr["Pm1"] is DBNull ? 0 : sdr["Pm1"].ToString().ToDouble(0),
                    //Pm1_Max = sdr["Pm1_Max"] is DBNull ? 0 : sdr["Pm1_Max"].ToString().ToDouble(0),
                    //Pm1_Min = sdr["Pm1_Min"] is DBNull ? 0 : sdr["Pm1_Min"].ToString().ToDouble(0),
                    Pm25 = sdr["Pm25"] is DBNull ? 0 : sdr["Pm25"].ToString().ToDouble(0),
                    //Pm2_5_Max = sdr["Pm2_5_Max"] is DBNull ? 0 : sdr["Pm2_5_Max"].ToString().ToDouble(0),
                    //Pm2_5_Min = sdr["Pm2_5_Min"] is DBNull ? 0 : sdr["Pm2_5_Min"].ToString().ToDouble(0),
                    Pm10 = sdr["Pm10"] is DBNull ? 0 : sdr["Pm10"].ToString().ToDouble(0),
                    //Pm10_Max = sdr["Pm10_Max"] is DBNull ? 0 : sdr["Pm10_Max"].ToString().ToDouble(0),
                    //Pm10_Min = sdr["Pm10_Min"] is DBNull ? 0 : sdr["Pm10_Min"].ToString().ToDouble(0),

                    Aqi = sdr["Aqi"] is DBNull ? 0 : sdr["Aqi"].ToString().ToDouble(0),
                    SO2 = sdr["SO2"] is DBNull ? 0 : sdr["SO2"].ToString().ToDouble(0),
                    NO2 = sdr["NO2"] is DBNull ? 0 : sdr["NO2"].ToString().ToDouble(0),
                    CO = sdr["CO"] is DBNull ? 0 : sdr["CO"].ToString().ToDouble(0),
                    O3 = sdr["O3"] is DBNull ? 0 : sdr["O3"].ToString().ToDouble(0),
                });
            }
            sdr.Close();

            return list;
        }

        #endregion
    }
}
